﻿Imports System.Data.SqlClient
 
Public Class CmdPro


    Shared cmd As SqlCommand = Nothing
    Shared da As SqlDataAdapter = Nothing
    Public Shared tran As SqlTransaction = Nothing
    Shared lastCmd As String = ""
    Public Shared Parameters As New Dictionary(Of String, Object)
    Public Shared Function generateCommand(ByVal sql As String, ByVal ParamArray args As Object()) As SqlCommand
        cmd = New SqlCommand(sql)
        cmd.Connection = Dbinterface.Connection()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = sql

        If Not (tran Is Nothing) Then
            cmd.Transaction = tran
        End If

        For Each p In Parameters
            If TypeOf p.Value Is DateTime Then
                cmd.Parameters.Add(p.Key, SqlDbType.DateTime).Value = p.Value
            ElseIf TypeOf p.Value Is Long OrElse TypeOf p.Value Is Integer Then
                cmd.Parameters.Add(p.Key, SqlDbType.Int).Value = p.Value
            ElseIf TypeOf p.Value Is Double OrElse TypeOf p.Value Is Decimal OrElse TypeOf p.Value Is Single Then
                cmd.Parameters.Add(p.Key, SqlDbType.Decimal).Value = p.Value
            ElseIf TypeOf p.Value Is Boolean Then
                cmd.Parameters.Add(p.Key, SqlDbType.Bit).Value = p.Value
            ElseIf p.Value Is Nothing OrElse p.Value Is DBNull.Value Then
                cmd.CommandText = cmd.CommandText.Replace(p.Key, "null")
            ElseIf TypeOf p.Value Is Byte() Then
                cmd.Parameters.Add(p.Key, SqlDbType.Image).Value = p.Value
            Else
                cmd.Parameters.Add(p.Key, SqlDbType.NVarChar).Value = p.Value
            End If
        Next

        If args.Length > 0 Then ' Have output parameter
            cmd.Parameters.Add(New SqlParameter(args(0).ToString, SqlDbType.Int, 0, ParameterDirection.Output, True, 0, 0, Nothing, DataRowVersion.Current, Nothing))
        End If


        lastCmd = cmd.CommandText

        Parameters.Clear()

        Return cmd
    End Function


    Public Shared Function ExecuteScalar(ByVal sql As String, ByVal ParamArray values As Object()) As Object
        Return generateCommand(sql, values).ExecuteScalar()

    End Function

    Public Shared Function ExecuteNonQuery(ByVal sql As String, ByVal ParamArray values As Object()) As Integer

        Try
            generateCommand(sql, values).ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return cmd.Parameters("@id").Value

    End Function
    Public Shared Function ExecuteNonQueryNoParameter(ByVal sql As String, ByVal ParamArray values As Object()) As Integer

        Return generateCommand(sql, values).ExecuteNonQuery()


    End Function

    Public Shared Function ExecuteDataTable(ByVal sql As String, ByVal ParamArray values As Object()) As DataTable



        If da Is Nothing Then
            da = New SqlDataAdapter()
        End If
        da.SelectCommand = generateCommand(sql, values)
        Dim dt As New DataTable()
        da.Fill(dt)
        Return dt

    End Function

    Public Shared Function ExecuteDataReader(ByVal sql As String, ByVal ParamArray values As Object()) As IDataReader
        Return generateCommand(sql, values).ExecuteReader()


    End Function

    Public Shared Function ExecuteDataSet(ByVal sql As String, ByVal ParamArray values As Object()) As DataSet
        If da Is Nothing Then
            da = New SqlDataAdapter()
        End If
        da.SelectCommand = generateCommand(sql, values)
        Dim ds As New DataSet()
        da.Fill(ds)
        Return ds
    End Function

    Public Shared Sub Begin()
        Try


            If tran Is Nothing Then
                tran = Dbinterface.Connection().BeginTransaction()
                If cmd Is Nothing Then
                    cmd = Dbinterface.Connection().CreateCommand()
                End If
                cmd.Transaction = tran
            End If
        Catch ex As Exception
            MsgBox("Make sure you have connection is connect to server." + vbCrLf + "All your data is going to rollback 'incomplete saved.' ", vbInformation, "Connection information")

        End Try
    End Sub
    Public Shared Sub Commit()
        Try
            tran.Commit()
            tran = Nothing
        Catch ex As Exception

        End Try

    End Sub
    Public Shared Sub Rollback()
        Try
            tran.Rollback()
            tran = Nothing
        Catch ex As Exception

        End Try

    End Sub
    Public Shared ReadOnly Property Trans() As SqlTransaction
        Get
            Return tran
        End Get
    End Property

End Class